/*
 * Copyright (c) 2006, 2022, webrx.cn All rights reserved.
 *
 */
package cn.webrx.db;

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.condition.DisabledIfSystemProperties;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * <p>Project: jdemo202203 - Demo
 * <p>Powered by webrx On 2022-10-31 09:03:04
 *
 * @author webrx [webrx@126.com]
 * @version 1.0
 * @since 17
 */

public class Demo {
    Connection conn;

    @BeforeEach
        //@Test
    void init() throws IOException {
        //加载src/main/resources/db.propertie文件配置 信息
        InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
        Properties prop = new Properties();
        prop.load(is);
        String driver = prop.getProperty("db.driver", "com.mysql.cj.jdbc.Driver");
        String url = prop.getProperty("db.url", "jdbc:mysql:/");
        String user = prop.getProperty("db.username", "root");
        String pwd = prop.getProperty("db.password", "");
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, pwd);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @AfterEach
    void close() {
        try {
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    void m3_insert_getId(){
        //statement
        //try{
        //    Statement st = conn.createStatement();
        //    int rows = st.executeUpdate("insert into student value(null,'王丽',18,'女',88,'会计')",Statement.RETURN_GENERATED_KEYS);
        //    System.out.println(rows);
        //
        //    ResultSet rs = st.getGeneratedKeys();
        //    rs.next();
        //    System.out.println(rs.getInt(1));
        //}catch(Exception e){
        //    e.printStackTrace();
        //}

        //preparestatement
        try{
            String sql = "insert into student(name,age,gender,score,sdept) value(?,?,?,?,?)";
            PreparedStatement st = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            st.setString(1,"赵丽");
            st.setInt(2,99);
            st.setString(3,"女");
            st.setString(5,"会计");
            st.setInt(4,99);
            int rows = st.executeUpdate();
            System.out.println(rows);

            ResultSet rs = st.getGeneratedKeys();
            rs.next();
            System.out.println(rs.getInt(1));
        }catch(Exception e){
            e.printStackTrace();
        }
    }



    @Test @DisplayName("PreparedStatement execute()方法")
    void m2_execute(){
        try{

            //create drop alter grant revoke
            //select delete update insert show
            String sql = "create database if not exists dd22";
            //sql = "show databases";
            //sql = "select * from student";
            PreparedStatement pst = conn.prepareStatement(sql);
           if(pst.execute()){
               ResultSet rs = pst.executeQuery();
               while(rs.next()){
                   System.out.println(rs.getString(1));
               }
           }else{
               System.out.printf("%s 语句成功执行。",sql);
           }
        }catch(Exception e){
            e.printStackTrace();
        }
    }


    @Test
    @DisplayName("PreparedStatement")
    void m2() {
        //PreparedStatement CRUD操作
        try {
            //建立 PreparedStatement语句  插入
            String sql = "insert into student value(null,?,?,?,?,?)";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1,"张丽");
            pst.setInt(2,25);
            pst.setString(3,"女");
            pst.setInt(4,95);
            pst.setString(5,"计算机科学");
            int rows = pst.executeUpdate();
            System.out.println(rows);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test @DisplayName("update")
    void m2_update(){
        //修改id = 124 成绩为99 专业为计算机科学
        try{
            String sql = "update student set score=?,sdept=? where id=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1,99);
            pst.setString(2,"计算机科学");
            pst.setInt(3,124);
            int rows = pst.executeUpdate();
            System.out.println(rows);
            m2_select();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    @Test @DisplayName("PreparedStatement delete")
    void m2_del(){
        try{
            //PreparedStatement executeUpdate()
            PreparedStatement pst = conn.prepareStatement("delete from student where id in (?,?)");
            pst.setInt(1,127);
            pst.setInt(2,128);

            //rows 删除的行数，如果是0就是没有删除，
            int rows = pst.executeUpdate();
            System.out.println(rows);

            m2_select();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    @Test
    void m2_select(){
        try{
            //PreparedStatement executeQuery() 查询
            PreparedStatement pst = conn.prepareStatement("select * from student order by score desc");
            ResultSet rs = pst.executeQuery();
            while(rs.next()){
                System.out.printf("id:%d，姓名：%s，专业：%s。%n",rs.getInt(1),rs.getString("name"),rs.getString("sdept"));
            }
        }catch(Exception e){
            e.printStackTrace();
        }
    }


    @Test
    @DisplayName("Statement")
    void m1() {
        //System.out.println(conn);
        //显示所有数据库

        try {
            Statement stmt = conn.createStatement();

            //execute() 方法用来执行DDL语句
            stmt.execute("create database if not exists dbaa");

            //executeQuery() 方法用来执行select show等查询语句，有查询结果
            ResultSet rs = stmt.executeQuery("show databases");
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }

            //插入数据 .executeUpdate() 方法 可以执行 insert delete update
            stmt.executeUpdate("insert book set name = '《JDBC入门操作案例》'");
            //删除
            stmt.executeUpdate("delete from book where id = 154");
            //修改
            stmt.executeUpdate("update book set name = '《MySQL项目实战2.0》' where id = 155");


            //实现查询
            rs = stmt.executeQuery("select id,name,price from book order by id desc");
            while (rs.next()) {
                System.out.println(rs.getInt(1) + rs.getString("name"));
            }

            rs.close();
            stmt.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }


    @Test
    void conn() {
        //加载驱动
        try {
            //Class.forName("com.mysql.cj.jdbc.Driver");

            //localhost主机 3306端口 null数据库 root账号 密码是默认的，所以没有写
            //Connection conn = DriverManager.getConnection("jdbc:mysql:?user=root");

            //url user password
            //Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbshop?useUnicode=true&characterEncoding=utf8","root","");

            String url = "jdbc:mysql://localhost:3306/dbshop";
            //url = "jdbc:mysql://localhost:3305/mysql";
            String user = "root";
            String password = "";
            Connection conn = DriverManager.getConnection(url, user, password);
            System.out.println(conn);

            System.out.println(conn.getMetaData().getDatabaseProductVersion());

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    @DisplayName("hello world test t1()")
    public void t() {
        System.out.println("hello world test...");
    }
}
